#! /usr/bin/env ruby

# Copyright (C) 2010 Tom Verbeure
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

require 'rubygems'

require 'net/https'
require 'tables'
require 'htmlentities'
require 'csv'

GMAIL_ACCOUNT = "XXXXXXXX@gmail.com"
GMAIL_PASSWD = "YYYYYYYYYYY"
TABLE_NAME = 'my_fusion_table'

trips = [
    {
        :id => 1,
        :name => 'Montana de Oro',
        :points => [ [-120.88469,35.27722], [-120.88476,35.27725], [-120.88475,35.27721], [-120.88557,35.27665] ],
        :rating => 3,
        :distance => 13.5,
        :pic_link => "http://www.mtbguru.com/images/cache/pics/30/5ea6eca2ffceba2dc3ce2d3d77be6a76e51b64.regular.JPEG",
    },
]

if 1 
    client = GData::Client::Tables.new( :source => 'my-fusiontables-demo')
    client.clientlogin(GMAIL_ACCOUNT, GMAIL_PASSWD)

    # Get all tables in my account. 
    resp = client.sql_get("SHOW TABLES")
    
    # Check if the desired table already exists. If so, remember the table_id.
    # Data return by fusiontables is in CSV format.
    table_id = nil
    CSV::Reader.parse(resp.body) do |row|
        table_id = row[0] if row[1] == TABLE_NAME
    end

    if table_id
        puts "EXISTING TABLE ID:", table_id
    else
        # Table didn't exist. Create a new table.
        fields = {
            'id'               => 'NUMBER',
            'name'             => 'STRING',
            'distance'         => 'NUMBER',
            'geom'             => 'LOCATION',
            'min_elevation'    => 'NUMBER',
            'max_elevation'    => 'NUMBER',
            'ascent'           => 'NUMBER',
            'descent'          => 'NUMBER',
            'trip_link'        => 'STRING',
            'pic_link'         => 'STRING',
            'rating'           => 'NUMBER',
                }

        # Construct an SQL query to create a new table.
        # Note: the columns are created in random order. This may not be desirable...
        fields_str = fields.collect{ |field,type| "#{field}:#{type}" }.join(" ,")
        table_id = client.create_table("CREATE TABLE #{TABLE_NAME}(#{fields_str})")

        puts "NEW TABLE ID:", table_id
    end

    # Extract all the columns from the table. A bit redundant if you just created the table, but
    # not so if the table already existed.
    fields = client.describe_table(table_id)

    # Used to convert UTF8 strings into HTML codes. Necessary to support funky characters in trip names.
    coder = HTMLEntities.new

    trips.each_with_index do |trip, cnt|
        puts "############################### #{cnt} : #{trip[:id]} #{trip[:name]}"
        
        trip_link = "http://www.mtbguru.com/trip/show_static/#{trip[:id]}"
    
        # Create a KML line string with the trip GPS track
        geom = "<LineString><coordinates>" + trip[:points].collect{ |p| "#{ p[0] },#{ p[1]}" }.join(" ") + "</coordinates></LineString>"
    
        # Fill in all the SQL fields.
        # This code should really move to tables.rb
        data = {
            'id'            => trip[:id],
            # Encode UTF8. rescue nil to avoid crash and burn on corrupted UTF8 entry in database...
            'name'          => (coder.encode(trip[:name], :hexadecimal) rescue nil),
            'distance'      => trip[:distance],
            'trip_link'     => trip_link,
            'pic_link'      => trip[:pic_link],
            'geom'          => geom,
            'rating'        => trip[:rating],
        }
    
        # Create SQL INSERT statement
        insert_fields = []
        insert_values = []
        data.each do |field, value|
            if (value)
                insert_fields << field
                type = fields[field][:type]
                insert_values << case fields[field][:type]
                    when "number" then      "#{value}"
                    when "string" then      "'#{value.gsub(/'/, "''")}'"    # SQL escape ' to ''
                    when "location" then    "'#{value.gsub(/'/, "''")}'"
                end
            end
        end
    
        sql = "INSERT INTO #{table_id} (#{ insert_fields.join(",") }) VALUES (#{ insert_values.join(",") })"
        begin
            # Do the insert...
            resp = client.sql_post(sql)
        rescue
            puts sql
            raise

            # NOTE:
            # Every once in a while, the fusion tables API times out with a ServerError. It typically happens once 
            # for every ~5000 API queries.
            # The code in here should retry such a query, but that's not implemented.
        end
    end
end

